package Cabbage.MySQLServerDAL;

import java.util.*;
import MySql.Data.MySqlClient.*;

/** 
 Copyright (C) 2006-2009 Xubinhua
 数据访问基础类(基于MYSQL)
 QQ 270970676
 Email xubinhua888@163.com
*/
public class DbHelperMySQL
{
	//数据库连接字符串(web.config或app.config来配置)




	//<add key="ConnectionString" value="Server=localhost;User Id=root;Password=root;Persist Security Info=True;Database=test" />		
	protected static String connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
	//="Server=127.0.0.1;User Id=root;Password=root;Persist Security Info=True;Database=ebao";
		//

	public static void setConnectionString(String value)
	{
		connectionString = value;
	}
	public static String getConnectionString()
	{
		return connectionString;
	}


	public DbHelperMySQL()
	{
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region 公用方法

	public static int GetMaxID(String FieldName, String TableName)
	{
		String strsql = "select max(" + FieldName + ")+1 from " + TableName;
		Object obj = GetSingle(strsql);
		if (obj == null)
		{
			return 1;
		}
		else
		{
			return Integer.parseInt(obj.toString());
		}
	}

	public static boolean Exists(String strSql, MySqlParameter... cmdParms)
	{
		Object obj = GetSingle(strSql, cmdParms);
		int cmdresult;
		if ((obj.equals(null)) || (obj.equals(System.DBNull.Value)))
		{
			cmdresult = 0;
		}
		else
		{
			cmdresult = Integer.parseInt(obj.toString());
		}
		if (cmdresult == 0)
		{
			return false;
		}
		else
		{
			return true;
		}
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region  执行简单SQL语句

	/** 
	 执行SQL语句，返回影响的记录数
	 
	 @param SQLString SQL语句
	 @return 影响的记录数
	 
	*/



	public static int ExecuteSql(String SQLString)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			try (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
			{
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					return rows;
				}
				catch (RuntimeException E)
				{
					connection.Close();
					throw E;
				}
			}
		}
	}

	/** 
	 执行多条SQL语句，实现数据库事务。
	 
	 @param SQLStringList 多条SQL语句		
	*/
	public static void ExecuteSqlTran(ArrayList SQLStringList)
	{
		try (MySqlConnection conn = new MySqlConnection(connectionString))
		{
			conn.Open();
			MySqlCommand cmd = new MySqlCommand();
			cmd.setConnection(conn);
			MySqlTransaction tx = conn.BeginTransaction();
			cmd.setTransaction(tx);
			try
			{
				for (int n = 0; n < SQLStringList.size(); n++)
				{
					String strsql = SQLStringList.get(n).toString();
					if (strsql.trim().length() > 1)
					{
						cmd.setCommandText(strsql);
						cmd.ExecuteNonQuery();
					}
				}
				tx.Commit();
			}
			catch (RuntimeException E)
			{
				tx.Rollback();
				throw E;
			}
		}
	}




	/** 
	 执行带一个存储过程参数的的SQL语句。
	 
	 @param SQLString SQL语句
	 @param content 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加
	 @return 影响的记录数
	*/
	public static int ExecuteSql(String SQLString, String content)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			MySqlCommand cmd = new MySqlCommand(SQLString, connection);
			System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
			myParameter.Value = content;
			cmd.getParameters().Add(myParameter);
			try
			{
				connection.Open();
				int rows = cmd.ExecuteNonQuery();
				return rows;
			}
			catch (RuntimeException E)
			{
				throw E;
			}
			finally
			{
				cmd.Dispose();
				connection.Close();
			}
		}
	}
	/** 
	 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
	 
	 @param strSQL SQL语句
	 @param fs 图像字节,数据库的字段类型为image的情况
	 @return 影响的记录数
	 
	*/



//C# TO JAVA CONVERTER WARNING: Unsigned integer types have no direct equivalent in Java:
//ORIGINAL LINE: public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
	public static int ExecuteSqlInsertImg(String strSQL, byte[] fs)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			MySqlCommand cmd = new MySqlCommand(strSQL, connection);
			System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
			myParameter.Value = fs;
			cmd.getParameters().Add(myParameter);
			try
			{
				connection.Open();
				int rows = cmd.ExecuteNonQuery();
				return rows;
			}
			catch (RuntimeException E)
			{
				throw E;
			}
			finally
			{
				cmd.Dispose();
				connection.Close();
			}
		}
	}





	/** 
	 执行一条计算查询结果语句，返回查询结果（object）。
	 
	 @param SQLString 计算查询结果语句
	 @return 查询结果（object）
	*/
	public static Object GetSingle(String SQLString)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			try (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
			{
				try
				{
					connection.Open();
					Object obj = cmd.ExecuteScalar();
					if ((obj.equals(null)) || (obj.equals(System.DBNull.Value)))
					{
						return null;
					}
					else
					{
						return obj;
					}
				}
				catch (RuntimeException e)
				{
					connection.Close();
					throw e;
				}
			}
		}
	}
	/** 
	 执行查询语句，返回SqlDataReader
	 
	 @param strSQL 查询语句
	 @return SqlDataReader
	*/
	public static MySqlDataReader ExecuteReader(String strSQL)
	{
		MySqlConnection connection = new MySqlConnection(connectionString);
		MySqlCommand cmd = new MySqlCommand(strSQL, connection);
		try
		{
			connection.Open();
			MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			return myReader;
		}
		catch (RuntimeException e)
		{
			throw e;
		}
		finally
		{
			connection.Close();
		}
	}
	/** 
	 执行查询语句，返回DataSet
	 
	 @param SQLString 查询语句
	 @return DataSet
	 
	*/



	public static DataSet Query(String SQLString)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			DataSet ds = new DataSet();
			try
			{
				connection.Open();
				MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
				command.Fill(ds, "ds");
			}
			catch (RuntimeException ex)
			{
				throw ex;
			}
			return ds;
		}
	}

	public static DataSet Query2(String SQLString)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			DataSet ds = new DataSet();
			try
			{
				connection.Open();
				MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
				command.Fill(ds, "ds");
			}
			catch (RuntimeException ex)
			{
				throw ex;
			}
			return ds;
		}
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region 执行带参数的SQL语句

	/** 
	 执行SQL语句，返回影响的记录数
	 
	 @param SQLString SQL语句
	 @return 影响的记录数
	 
	*/



	public static int ExecuteSql(String SQLString, MySqlParameter... cmdParms)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			try (MySqlCommand cmd = new MySqlCommand())
			{
				try
				{
					PrepareCommand(cmd, connection, null, SQLString, cmdParms);
					int rows = cmd.ExecuteNonQuery();
					cmd.getParameters().Clear();
					return rows;
				}
				catch (RuntimeException E)
				{
					throw E;
				}
			}
		}
	}


	/** 
	 执行多条SQL语句，实现数据库事务。
	 
	 @param SQLStringList SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）
	*/
	public static void ExecuteSqlTran(Hashtable SQLStringList)
	{
		try (MySqlConnection conn = new MySqlConnection(connectionString))
		{
			conn.Open();
			try (MySqlTransaction trans = conn.BeginTransaction())
			{
				MySqlCommand cmd = new MySqlCommand();
				try
				{
					//循环
					for (DictionaryEntry myDE : SQLStringList)
					{
						String cmdText = myDE.Key.toString();
						MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
						PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
						int val = cmd.ExecuteNonQuery();
						cmd.getParameters().Clear();

						trans.Commit();
					}
				}
				catch (RuntimeException ex)
				{
					trans.Rollback();
					throw ex;
				}
			}
		}
	}


	/** 
	 执行一条计算查询结果语句，返回查询结果（object）。
	 
	 @param SQLString 计算查询结果语句
	 @return 查询结果（object）
	*/
	public static Object GetSingle(String SQLString, MySqlParameter... cmdParms)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			try (MySqlCommand cmd = new MySqlCommand())
			{
				try
				{
					PrepareCommand(cmd, connection, null, SQLString, cmdParms);
					Object obj = cmd.ExecuteScalar();
					cmd.getParameters().Clear();
					if ((obj.equals(null)) || (obj.equals(System.DBNull.Value)))
					{
						return null;
					}
					else
					{
						return obj;
					}
				}
				catch (RuntimeException e)
				{
					throw e;
				}
			}
		}
	}

	/** 
	 执行查询语句，返回SqlDataReader
	 
	 @param strSQL 查询语句
	 @return SqlDataReader
	*/
	public static MySqlDataReader ExecuteReader(String SQLString, MySqlParameter... cmdParms)
	{
		MySqlConnection connection = new MySqlConnection(connectionString);
		MySqlCommand cmd = new MySqlCommand();
		try
		{
			PrepareCommand(cmd, connection, null, SQLString, cmdParms);
			MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			cmd.getParameters().Clear();
			return myReader;
		}
		catch (RuntimeException e)
		{
			throw e;
		}
		finally
		{
			connection.Close();
		}

	}

	/** 
	 执行查询语句，返回DataSet
	 
	 @param SQLString 查询语句
	 @return DataSet
	*/
	public static DataSet Query(String SQLString, MySqlParameter... cmdParms)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			MySqlCommand cmd = new MySqlCommand();
			PrepareCommand(cmd, connection, null, SQLString, cmdParms);
			try (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
			{
				DataSet ds = new DataSet();
				try
				{
					da.Fill(ds, "ds");
					cmd.getParameters().Clear();
				}
				catch (RuntimeException ex)
				{
					throw ex;
				}
				return ds;
			}
		}
	}


	private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, String cmdText, MySqlParameter[] cmdParms)
	{
		if (conn.getState() != ConnectionState.Open)
		{
			conn.Open();
		}
		cmd.setConnection(conn);
		cmd.setCommandText(cmdText);
		if (trans != null)
		{
			cmd.setTransaction(trans);
		}
		cmd.setCommandType(CommandType.Text); //cmdType;
		if (cmdParms != null)
		{
			for (MySqlParameter parm : cmdParms)
			{
				cmd.getParameters().Add(parm);
			}
		}
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region 存储过程操作

	/** 
	 执行存储过程
	 
	 @param storedProcName 存储过程名
	 @param parameters 存储过程参数
	 @return SqlDataReader
	*/
	public static MySqlDataReader RunProcedure(String storedProcName, IDataParameter[] parameters)
	{
		MySqlConnection connection = new MySqlConnection(connectionString);

		MySqlDataReader returnReader;
		connection.Open();
		MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
		command.setCommandType(CommandType.StoredProcedure);
		returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
		return returnReader;

	}


	/** 
	 执行存储过程
	 
	 @param storedProcName 存储过程名
	 @param parameters 存储过程参数
	 @param tableName DataSet结果中的表名
	 @return DataSet
	*/
	public static DataSet RunProcedure(String storedProcName, IDataParameter[] parameters, String tableName)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			DataSet dataSet = new DataSet();
			connection.Open();
			MySqlDataAdapter sqlDA = new MySqlDataAdapter();
			sqlDA.setSelectCommand(BuildQueryCommand(connection, storedProcName, parameters));
			sqlDA.Fill(dataSet, tableName);
			connection.Close();
			return dataSet;
		}
	}


	/** 
	 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
	 
	 @param connection 数据库连接
	 @param storedProcName 存储过程名
	 @param parameters 存储过程参数
	 @return SqlCommand
	*/
	private static MySqlCommand BuildQueryCommand(MySqlConnection connection, String storedProcName, IDataParameter[] parameters)
	{
		MySqlCommand command = new MySqlCommand(storedProcName, connection);
		command.setCommandType(CommandType.StoredProcedure);
		for (MySqlParameter parameter : parameters)
		{
			command.getParameters().Add(parameter);
		}
		return command;
	}

	/** 
	 执行存储过程，返回影响的行数		
	 
	 @param storedProcName 存储过程名
	 @param parameters 存储过程参数
	 @param rowsAffected 影响的行数
	 @return 
	*/
	public static int RunProcedure(String storedProcName, IDataParameter[] parameters, tangible.RefObject<Integer> rowsAffected)
	{
		try (MySqlConnection connection = new MySqlConnection(connectionString))
		{
			int result;
			connection.Open();
			MySqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
			rowsAffected.argValue = command.ExecuteNonQuery();
			result = (Integer)command.getParameters().getItem("ReturnValue").getValue();
			//Connection.Close();
			return result;
		}
	}

	/** 
	 创建 SqlCommand 对象实例(用来返回一个整数值)	
	 
	 @param storedProcName 存储过程名
	 @param parameters 存储过程参数
	 @return SqlCommand 对象实例
	*/
	private static MySqlCommand BuildIntCommand(MySqlConnection connection, String storedProcName, IDataParameter[] parameters)
	{
		MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
		command.getParameters().Add(new MySqlParameter("ReturnValue", MySqlDbType.Int32, 4, ParameterDirection.ReturnValue, false, 0, 0, "", DataRowVersion.Default, null));
		return command;
	}
//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion
}